package org.zoomdev.zoom.dao.ddl.utils;

import org.zoomdev.zoom.dao.ddl.SqlType;
import org.zoomdev.zoom.dao.driver.MysqlDriver;
import org.zoomdev.zoom.dao.meta.ColumnMeta;
import org.zoomdev.zoom.dao.meta.IndexMeta;
import org.zoomdev.zoom.dao.meta.TableKeyMeta;
import org.zoomdev.zoom.dao.meta.TableMeta;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public final class H2MetaUtils {

    public static TableMeta getTableMeta(Connection conn, String tableName) throws SQLException {
        try {
            TableMeta tableMeta = new TableMeta();
            tableMeta.setName(tableName);
            fillTableMeta(conn, tableMeta);
            return tableMeta;
        } catch (SQLException e) {
            throw new SQLException("处理" + tableName + "发生异常", e);
        }
    }

    /**
     * 获取表元数据（H2适配）
     */
    public static void fillTableMeta(Connection conn, TableMeta tableMeta) throws SQLException {
        String tableName = tableMeta.getName();
        fillTableMetaProperties(conn, tableMeta);
        tableMeta.setColumns(getColumnsFromInfoSchema(conn, tableName));
        tableMeta.setPrimaryKeys(getPrimaryKeysFromInfoSchema(conn, tableName));
        tableMeta.setIndexes(getIndexesFromInfoSchema(conn, tableName));
    }

    public static void fillTableMeta(Connection conn, TableKeyMeta tableMeta) throws SQLException {
        String tableName = tableMeta.getName();
        tableMeta.setColumns(MetaUtils.getColumnTypes(conn, tableMeta.getName()));
        tableMeta.setPrimaryKeys(getPrimaryKeysFromInfoSchema(conn, tableName));
    }

    /**
     * 获取表基本属性（H2适配）
     */
    private static void fillTableMetaProperties(Connection conn, TableMeta tableMeta) throws SQLException {
        String schema = conn.getSchema();
        String tableName = tableMeta.getName();
        // H2使用TABLE_SCHEMA代替DATABASE_NAME[3](@ref)
        String sql = "SELECT TABLE_COMMENT, STORAGE_TYPE FROM INFORMATION_SCHEMA.TABLES " +
                "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, schema);
            stmt.setString(2, tableName);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    tableMeta.setComment(rs.getString("TABLE_COMMENT"));
                    // H2使用STORAGE_TYPE代替ENGINE[3](@ref)
                    tableMeta.setEngine(rs.getString("STORAGE_TYPE"));
                } else {
                    tableMeta.setComment("");
                    tableMeta.setEngine("PERSISTENT"); // H2默认存储类型
                }
            }
        }
    }

    /**
     * 从INFORMATION_SCHEMA获取列信息（H2适配）
     */
    private static ColumnMeta[] getColumnsFromInfoSchema(
            Connection conn, String tableName) throws SQLException {
        List<ColumnMeta> columns = new ArrayList<>();
        String schema = conn.getSchema();

        // H2的COLUMNS表结构略有不同[3,6](@ref)
        String sql = "SELECT " +
                "  COLUMN_NAME, " +
                "  TYPE_NAME, " +         // H2使用TYPE_NAME代替COLUMN_TYPE
                "  CHARACTER_MAXIMUM_LENGTH, " +
                "  NUMERIC_PRECISION, " +
                "  NUMERIC_SCALE, " +
                "  IS_NULLABLE, " +
                "  COLUMN_DEFAULT, " +
                "  REMARKS AS COLUMN_COMMENT, " + // H2使用REMARKS存储注释
                "  IS_AUTOINCREMENT " +   // H2特有字段
                "FROM INFORMATION_SCHEMA.COLUMNS " +
                "WHERE TABLE_SCHEMA = ? " +
                "AND TABLE_NAME = ? " +
                "ORDER BY ORDINAL_POSITION";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, schema);
            stmt.setString(2, tableName);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    ColumnMeta meta = new ColumnMeta();
                    meta.setName(rs.getString("COLUMN_NAME"));
                    meta.setType(parseH2DataType(rs.getString("TYPE_NAME")));

                    // 处理长度和精度
                    if (meta.getType().hasLength()) {
                        long charLength = rs.getLong("CHARACTER_MAXIMUM_LENGTH");
                        if (!rs.wasNull()) meta.setSize(charLength);

                        int numericPrecision = rs.getInt("NUMERIC_PRECISION");
                        if (!rs.wasNull()) meta.setSize(numericPrecision);
                    }

                    int numericScale = rs.getInt("NUMERIC_SCALE");
                    if (!rs.wasNull()) meta.setScale(numericScale);

                    // H2特有自增字段处理[6](@ref)
                    String isAutoIncrement = rs.getString("IS_AUTOINCREMENT");
                    meta.setAutoIncrement("YES".equalsIgnoreCase(isAutoIncrement));

                    meta.setNotNull(!"YES".equalsIgnoreCase(rs.getString("IS_NULLABLE")));
                    meta.setDefaultValue(parseH2DefaultValue(rs));
                    meta.setComment(rs.getString("COLUMN_COMMENT"));

                    columns.add(meta);
                }
            }
        }
        return columns.toArray(new ColumnMeta[0]);
    }

    /**
     * 获取主键信息（H2适配）
     */
    private static String[] getPrimaryKeysFromInfoSchema(
            Connection conn, String tableName) throws SQLException {
        List<String> keys = new ArrayList<>();
        String schema = conn.getSchema();

        // H2使用CONSTRAINTS表获取主键[3](@ref)
        String sql = "SELECT COLUMN_NAME " +
                "FROM INFORMATION_SCHEMA.CONSTRAINTS " +
                "WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' " +
                "AND TABLE_SCHEMA = ? " +
                "AND TABLE_NAME = ? " +
                "ORDER BY ORDINAL_POSITION";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, schema);
            stmt.setString(2, tableName);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    keys.add(rs.getString("COLUMN_NAME"));
                }
            }
        }
        return keys.toArray(new String[0]);
    }

    /**
     * 获取索引信息（H2适配）
     */
    private static IndexMeta[] getIndexesFromInfoSchema(
            Connection conn, String tableName) throws SQLException {
        Map<String, IndexMeta> indexMap = new LinkedHashMap<>();
        String schema = conn.getSchema();

        // H2使用INDEXES表获取索引信息[3](@ref)
        String sql = "SELECT " +
                "  INDEX_NAME, " +
                "  COLUMN_NAME, " +
                "  ORDINAL_POSITION, " +
                "  NON_UNIQUE " +
                "FROM INFORMATION_SCHEMA.INDEXES " +
                "WHERE TABLE_SCHEMA = ? " +
                "AND TABLE_NAME = ? " +
                "AND PRIMARY_KEY = FALSE " + // 排除主键
                "ORDER BY INDEX_NAME, ORDINAL_POSITION";
        Map<String, List<Map<String, Object>>> indexes = new LinkedHashMap<>();
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, schema);
            stmt.setString(2, tableName);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String indexName = rs.getString("INDEX_NAME");
                    boolean nonUnique = rs.getBoolean("NON_UNIQUE");
                    String columnName = rs.getString("COLUMN_NAME");
                    Map<String, Object> map = new HashMap<>();
                    map.put("name", indexName);
                    map.put("unique", !nonUnique);
                    map.put("column", columnName);

                    indexes.computeIfAbsent(indexName, k -> new ArrayList<>())
                            .add(map);
                }
            }
        }

        List<IndexMeta> indexMetas = new ArrayList<>();
        indexes.forEach((k, v) -> {
            IndexMeta meta = new IndexMeta();
            meta.setName(k);
            meta.setColumns(v.stream().map(m -> (String) m.get("column")).toArray(String[]::new));
            meta.setUnique((Boolean) v.get(0).get("unique"));
            indexMetas.add(meta);
        });

        return indexMetas.toArray(new IndexMeta[0]);
    }

    /**
     * H2数据类型解析（适配H2特有类型）[6](@ref)
     */
    private static SqlType parseH2DataType(String dataType) {
        String normalized = dataType.trim().toUpperCase();

        // 提取基本类型（移除长度信息）
        String baseType = normalized.contains("(") ?
                normalized.substring(0, normalized.indexOf('(')) : normalized;

        // H2特有类型处理[6](@ref)
        switch (baseType) {
            // 数值类型
            case "BOOLEAN":
            case "BOOL":
                return SqlType.BOOLEAN;
            case "TINYINT":
                return SqlType.TINYINT;
            case "SMALLINT":
                return SqlType.SMALLINT;
            case "INT":
            case "INTEGER":
            case "MEDIUMINT": // H2将MEDIUMINT映射为INT
                return SqlType.INT;
            case "BIGINT":
                return SqlType.BIGINT;
            case "DECIMAL":
            case "NUMERIC":
                return SqlType.DECIMAL;
            case "REAL":
                return SqlType.FLOAT;
            case "DOUBLE":
                return SqlType.DOUBLE;

            // 字符串类型
            case "CHAR":
            case "CHARACTER":
                return SqlType.CHAR;
            case "VARCHAR":
                return SqlType.VARCHAR;
            case "VARCHAR_IGNORECASE": // H2特有类型[6](@ref)
                return SqlType.VARCHAR;
            case "CLOB":
                return SqlType.CLOB;

            // 二进制类型
            case "BINARY":
            case "VARBINARY":
                return SqlType.BLOB;
            case "BLOB":
                return SqlType.BLOB;
            case "UUID": // H2特有类型[6](@ref)
                return SqlType.VARCHAR;

            // 时间类型
            case "DATE":
                return SqlType.DATE;
            case "TIME":
                return SqlType.TIME;
            case "TIMESTAMP":
            case "DATETIME": // H2中DATETIME映射为TIMESTAMP
                return SqlType.TIMESTAMP;

            // 其他类型
            case "ARRAY":
                return SqlType.BLOB;
            case "GEOMETRY":
                return SqlType.BLOB;
            case "JSON":
                return SqlType.JSON;

            default:
                // 尝试识别ENUM类型 (H2通过CREATE TYPE创建)
                if (normalized.startsWith("ENUM")) {
                    return SqlType.ENUM;
                }
                return SqlType.VARCHAR; // 默认类型
        }
    }

    /**
     * H2默认值解析
     */
    private static String parseH2DefaultValue(ResultSet rs) throws SQLException {
        String def = rs.getString("COLUMN_DEFAULT");
        if (def == null) return null;

        // 处理H2特有的自增标识
//        if ("AUTO_INCREMENT".equalsIgnoreCase(def) ||
//                "IDENTITY".equalsIgnoreCase(def)) {
//            return DefaultValue.AUTO_INCREMENT;
//        }

        // 处理时间函数
        if (def.contains("CURRENT_TIMESTAMP")) {
            return MysqlDriver.CURRENT_TIMESTAMP;
        }

        // 其他情况返回字面值
        return def;
    }
}